<?php

namespace App\Http\Controllers;

use App\Models\Book;
use App\Models\Spider;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class BookController extends Controller
{
    public function getBooks(Request $request)
    {
        if (!$request->jd_id && !$request->dd_id) return response()->json([
            "status" => 'failed',
            "message" => 'Please specify job id'
        ], 400);

        $sql = [];
        if ($request->jd_id) {
            $jdSpider = Spider::findOrFail($request->jd_id);
            if ($jdSpider->type != 'jd') return response()->json([
                "status" => 'failed',
                "message" => 'jd_id illegal'
            ]);
            array_push($sql, '
                (select ( @i := @i + 1 ) idx,
                    jd_price price, isbn, jd_id, null dd_id,
                    comment_count jd_c,
                    average_score jd_s,
                    NULL dd_c,
                    NULL dd_s
                    from j_d_books,( SELECT @i := 0 ) AS i where job=' . (int)$request->jd_id . '
                )
            ');
        }
        if ($request->dd_id) {
            $jdSpider = Spider::findOrFail($request->dd_id);
            if ($jdSpider->type != 'dd') return response()->json([
                "status" => 'failed',
                "message" => 'dd_id illegal'
            ]);
            array_push($sql, '
                (select ( @j := @j + 1 ) idx,
                    dd_price price, isbn, null jd_id, dd_id,
                    NULL jd_c,
                    NULL jd_s,
                    comment_count dd_c,
                    average_score dd_s
                    from d_d_books,( SELECT @j := 0 ) AS j where job=' . (int)$request->dd_id . '
                )
            ');
        }
        if (!$request->sort_method) {
            return response()->json([
                "status" => 'failed',
                "message" => 'Sort method required'
            ], 400);
        }
        $order=[
            "idx"=>"min_idx",
            "min_p"=>"min_p",
            "max_p"=>"max_p",
            "avg_p"=>"avg_p",
            "mention"=>"mention",
            "publish_date"=>"publish_date",
            "comment_count"=>"comment_count",
            "average_score"=>"average_score",
        ][$request->sort_method];
        if (!$order) {
            return response()->json([
                "status" => 'failed',
                "message" => 'Sort method illegal'
            ], 400);
        }
        $desc=$request->desc=='true'?'DESC':'ASC';
        return DB::table(DB::raw(
            '(select
                    isbn,
                    min(price) min_p,
                    max(price) max_p,
                    cast(avg(price) as decimal(8, 2)) avg_p,
                    count(*) mention,
                    min(idx) min_idx,
                    COALESCE(sum(jd_c),0)+COALESCE(sum(dd_c),0) comment_count,
                    (COALESCE(sum(jd_s),0)+COALESCE(sum(dd_s),0)*20)/(count(jd_s)+count(dd_s)) average_score
                    from (
                        ' . join('union all', $sql) . '
                ) crawl group by isbn) p_isbn'))
            ->crossJoin(DB::raw('books b'))
            ->whereRaw('b.isbn = p_isbn.isbn')
            ->orderBy($order, $desc)
            ->orderBy('id', $desc)
            ->paginate(15);
    }
    public function getBook(Request $request){
        return Book::where('isbn', '=', $request->isbn)->with('jd_books')->with('dd_books')->firstOrFail();
    }
    public function getProductInfo(Request $request){
        $book = Book::query()->where('isbn','=',$request->isbn)->firstOrFail();
        return [
            'unique_jd_books'=>$book->unique_jd_books,
            'unique_dd_books'=>$book->unique_dd_books
        ];
    }

    public function getDatabaseQuery(Request $request){
        $conditions = [];
        foreach ($request->key as $condition) {
            $subConditions = [];
            if ($condition['from'] == 'all') {
                $froms = ['name', 'author', 'digest', 'publisher'];
            } else if ($condition['from'] == 'author') {
                $froms = ['translator', 'author'];
            } else {
                $froms = [$condition['from']];
            }
            foreach ($froms as $from) {
                foreach ($condition['query'] as $key) {
                    array_push($subConditions, $from . ' like \'%' . $key . '%\'');
                }
            }
            array_push($conditions, '(' . join(' OR ', $subConditions) . ')');
        }

        $finalCondition = join(' AND ', $conditions);

        $isbns = function ($query) use ($finalCondition) {
            return $query->from('books')
                ->whereRaw($finalCondition)
                ->select('isbn');
        };

        $dd_books_id = function ($query) use ($isbns) {
            $query->from('d_d_books')->whereIn('isbn', $isbns)
                ->groupBy('dd_id')
                ->selectRaw('min(id)');
        };

        $dd_books_info = DB::table('d_d_books', 'ddb2')
            ->select([
                'isbn',
                'dd_price as price',
                DB::raw('COALESCE(ddb2.comment_count, 0) as comment_count'),
                DB::raw('COALESCE(ddb2.average_score, 0)*20 as average_score')
            ])
            ->whereIn('id', $dd_books_id);

        $jd_books_id = function ($query) use ($isbns) {
            $query->from('j_d_books')->whereIn('isbn', $isbns)
                ->groupBy('jd_id')
                ->selectRaw('min(id)');
        };

        $jd_books_info = DB::table('j_d_books', 'jdb2')
            ->select([
                'isbn',
                'jd_price as price',
                DB::raw('COALESCE(jdb2.comment_count, 0) as comment_count'),
                DB::raw('COALESCE(jdb2.average_score, 0) as average_score')
            ])
            ->whereIn('id', $jd_books_id);

        $with_books_meta = Book::query()->rightJoinSub(function ($query) use ($dd_books_info, $jd_books_info){
            $query->fromSub($dd_books_info->unionAll($jd_books_info), 'books')->groupBy('isbn')
                ->select(
                    'isbn',
                    DB::raw('cast(avg(price) as decimal(8, 2)) avg_p'),
                    DB::raw('cast(min(price) as decimal(8, 2)) min_p'),
                    DB::raw('cast(max(price) as decimal(8, 2)) max_p'),
                    DB::raw('sum(comment_count) comment_count'),
                    DB::raw('avg(average_score) average_score'),
                    DB::raw('count(*) AS `mention`')
                );
        }, 'group', 'books.isbn', '=', 'group.isbn');

        return ['result'=>$with_books_meta, "count"=>$isbns(DB::table('books'))];
    }

    public function databaseSearch(Request $request) {
        try{
            $order = [
                "avg_p" => "avg_p",
                "mention" => "mention",
                "publish_date" => "publish_date",
                "comment_count" => "comment_count",
                "average_score" => "average_score",
            ][$request->sort_method];
        }catch (\Exception $ex){
            return response()->json([
                "status" => 'failed',
                "message" => 'Sort method illegal'
            ], 400);
        }

        DB::enableQueryLog();
        $desc = $request->desc == 'true' ? 'DESC' : 'ASC';
        $query=$this->getDatabaseQuery($request);
        return [
            'data'=>$query['result']
                ->orderBy($request->sort_method, $desc)
                ->skip(($request->page-1)*10)
                ->limit(10)->get(),
            'total'=>$query['count']->count(),
            'log' => DB::getQueryLog(),
            'current_page' => $request->page,
        ];
    }

    public function searchStatistics(Request $request){
        $query=$this->getDatabaseQuery($request);
        $sql=$query['result']->toSql();
        $result = json_decode(`python3 ../statistics/getStatistics.py "$sql"`);
        if (!$result) {
            $result = json_decode(`python ../statistics/getStatistics.py "$sql"`);
        }
        return [
            'statistics'=>  $result,
            'sql'=> $sql
        ];
    }
}
